import connection from "./dbConnection.js";
import {
  LeetCodeUser,
  LeetCodeTransformer,
  CodeforcesUser,
  CodeforcesTransformer,
  AtCoderUser,
  AtCoderTransformer,
  AtCoderProblem,
  Avatar,
} from "./dataGetting/index.js";
import { allPlatform } from "./api/tools/sqlString.js";

async function logError(errorObject) {
  try {
    await connection.query(`INSERT INTO error_log SET ?`, errorObject);
  } catch (err) {
    console.error(err);
  }
}

// Codeforces refreshing
async function refreshCodeforces() {
  const handleNames = ['codeforces_handle', 'codeforces_handle2', 'codeforces_handle3']
  handleNames.forEach(async (handleName, cnt) => {
    const codeforcesRow = await connection.query(
      `SELECT id, ${handleName} FROM user WHERE ${handleName} is not NULL`
    );
    codeforcesRow.forEach((row, index) => {
      setTimeout(async () => {
        const user = new CodeforcesUser(row[handleName]);
        try {
          const submissionList = await user.getSubmissionList();
          if (!submissionList.length) {
            return;
          }

          const { codeforcesSubmission, codeforcesProblem, problemTag } =
            CodeforcesTransformer.transformSubmissions(submissionList, row.id);

          const submissionQuery = `INSERT INTO submission 
          (submission_id, user_id, submit_time, codeforces_problem_id, status, rating)
          VALUES ${connection.escape(codeforcesSubmission)} ON DUPLICATE KEY UPDATE
          rating = VALUES(rating), status = VALUES(status);`;

          await connection.query(submissionQuery);

          const problemQuery = `INSERT INTO problem
          (codeforces_problem_id, title, rating, tags)
          VALUES ${connection.escape(codeforcesProblem)}
          ON DUPLICATE KEY UPDATE rating = VALUES(rating),
          tags = VALUES(tags);`;
          await connection.query(problemQuery);

          const tagQuery =
            `INSERT IGNORE INTO problem_tag
           (codeforces_problem_id, tag)
           VALUES ` + connection.escape(problemTag);
          await connection.query(tagQuery);

          console.log(`Codeforces: ${row[handleName]}`);
        } catch (err) {
          console.error(`ERROR of Codeforces: ${row[handleName]}`);
          console.error(err);
          logError({
            action: `Refresh Codeforces Submissions`,
            platform: `Codeforces`,
            handle: row[handleName],
            error: err,
          });
        }
      }, (index * 3 + cnt) * 2000);
    });
  })
}

// LeetCode refreshing
async function refreshLeetcode() {
  const handleNames = ['leetcode_handle', 'leetcode_handle2', 'leetcode_handle3']
  handleNames.forEach(async (handleName) => {
    const leetcodeRow = await connection.query(
      `SELECT id, ${handleName} FROM user WHERE ${handleName} is not NULL`
    );

    leetcodeRow.forEach(async (row) => {
      const user = new LeetCodeUser(row[handleName]);
      try {
        const submissionList = await user.getRecentSubmissionList();
        if (!submissionList.length) {
          return;
        }

        const { leetCodeSubmission, leetCodeProblem, problemTag } =
          LeetCodeTransformer.transformSubmissions(submissionList, row.id);

        const submissionQuery = `INSERT INTO submission 
		(submission_id, user_id, submit_time, leetcode_problem_id, status, rating)
		VALUES  ${connection.escape(leetCodeSubmission)} ON DUPLICATE KEY UPDATE
		rating = VALUES(rating), status = VALUES(status)`;
        await connection.query(submissionQuery);

        const problemQuery =
          `INSERT IGNORE INTO problem
		(leetcode_problem_id, title, title_slug, rating, tags)
		VALUES ` + connection.escape(leetCodeProblem);
        await connection.query(problemQuery);

        const tagQuery =
          `INSERT IGNORE INTO problem_tag
	 	(leetcode_problem_id, tag)
	 	VALUES ` + connection.escape(problemTag);
        await connection.query(tagQuery);

        console.log(`LeetCode: ${row[handleName]}`);
      } catch (err) {
        console.error(`ERROR of LeetCode: ${row[handleName]}`);
        console.error(err);
        logError({
          action: `Refresh LeetCode Submissions`,
          platform: `LeetCode`,
          handle: row[handleName],
          error: err,
        });
      }
    });
  })
}

// Atcoder submission refreshing
async function refreshAtcoderSubmissions() {
  const handleNames = ['atcoder_handle', 'atcoder_handle2', 'atcoder_handle3']
  handleNames.forEach(async (handleName) => {
    const atcoderRow = await connection.query(
      `SELECT id, ${handleName} FROM user WHERE ${handleName} is not NULL`
    );
    atcoderRow.forEach(async (row) => {
      const latestAtcoderTime = await connection.query(`SELECT MAX(submit_time) max_time FROM submission 
      WHERE user_id = ${row.id} AND atcoder_problem_id IS NOT NULL`)
      const user = new AtCoderUser(row[handleName]);
      try {
        const submissionList = await user.getSubmissionList(latestAtcoderTime[0]?.max_time)
        if (!submissionList.length) {
          return;
        }

        const { atCoderSubmission } =
          AtCoderTransformer.transformSubmissions(submissionList, row.id);

        const submissionQuery = `INSERT INTO submission 
      (submission_id, user_id, submit_time, atcoder_problem_id, status, rating)
      VALUES  ${connection.escape(atCoderSubmission)} ON DUPLICATE KEY UPDATE
      rating = VALUES(rating), status = VALUES(status)`;
        await connection.query(submissionQuery);

        console.log(`Atcoder Submission: ${row[handleName]}`);
      } catch (err) {
        console.error(`ERROR of AtCoder: ${row[handleName]}`);
        console.error(err);
        logError({
          action: `Refresh Atcoder Submissions`,
          platform: `AtCoder`,
          handle: row[handleName],
          error: err,
        });
      }
    });
  })
}

// Atcoder problem refreshing
async function refreshAtcoderProblems() {
  const originProblemList = await AtCoderProblem.getList();
  const { atCoderProblems } = AtCoderTransformer.transformProblems(originProblemList);
  atCoderProblems.forEach(async (problem) => {

    const problemQuery = `INSERT INTO problem
    (atcoder_problem_id, title, rating)
    VALUES (${connection.escape(problem)})
    ON DUPLICATE KEY UPDATE rating = VALUES(rating)`;

    await connection.query(problemQuery);
  })
}

async function refreshUserDailyStatus() {
  try {
    await connection.query(`CALL refresh_user_daily_status`);
    console.log("Refresh user_daily_status successfully.");
  } catch (err) {
    console.error(err);
    logError({
      action: `Refresh user daily status`,
      error: err,
    });
  }
}

function transformHandleToAvatar(handle) {
  return handle.replace(/(handle)(\d*)$/, 'avatar$2');
}


async function refreshLeetcodeAvatar() {
  const handleNames = ['leetcode_handle', 'leetcode_handle2', 'leetcode_handle3']
  handleNames.forEach(async (handleName) => {
    const avatarName = transformHandleToAvatar(handleName)

    const leetcodeRow = await connection.query(
      `SELECT id, ${handleName} FROM user WHERE ${handleName} is not NULL`
    );
    leetcodeRow.forEach(async (row) => {
      try {
        const leetcodeAvatar = await Avatar.getLeetcodeUserAvatar(
          row[handleName]
        );
        await connection.query(
          `UPDATE user SET ${avatarName} = '${leetcodeAvatar}'
			WHERE ${handleName} = '${row[handleName]}'`
        );
        console.log(`${row[handleName]}: ${leetcodeAvatar}`);
      } catch (err) {
        console.error(`ERROR of LeetCode: ${row[handleName]}`);
        console.error(err);
        logError({
          action: `Get LeetCode Avatar`,
          platform: `LeetCode`,
          handle: row[handleName],
          error: err,
        });
      }
    });
  })
}

async function refreshCodeforcesAvatar() {
  const handleNames = ['codeforces_handle', 'codeforces_handle2', 'codeforces_handle3']
  handleNames.forEach(async (handleName) => {
    const avatarName = transformHandleToAvatar(handleName)
    try {
      const codeforcesRow = await connection.query(
        `SELECT ${handleName} FROM user WHERE ${handleName} is not NULL`
      );
      codeforcesRow.forEach((row, index) => {
        setTimeout(async () => {
          try {
            const handle = row[handleName];
            const avatar = await Avatar.getCodeforcesUserAvatar(handle);
            await connection.query(`UPDATE user SET ${avatarName} = '${avatar}'
        	WHERE ${handleName} = '${handle}'`);
            console.log(`Avatar: ${handle} ${avatar}`);
          } catch (err) {
            console.error(`ERROR of Codeforces Avatar: ${row[handleName]}`);
            console.error(err);
            logError({
              action: `Get Codeforces Avatar`,
              platform: `Codeforces`,
              handle: row[handleName],
              error: err,
            });
          }
        }, index * 2000);
      });
    } catch (err) {
      console.error(err);
    }
  })
}

async function refreshRating() {
  const rows = await connection.query(
    `SELECT id, ${allPlatform} FROM user`
  );

  const handleFields = [
    { platform: 'Codeforces', handles: ['codeforces_handle', 'codeforces_handle2', 'codeforces_handle3'], ratingFields: ['codeforces_rating', 'codeforces_rating2', 'codeforces_rating3'] },
    { platform: 'LeetCode', handles: ['leetcode_handle', 'leetcode_handle2', 'leetcode_handle3'], ratingFields: ['leetcode_rating', 'leetcode_rating2', 'leetcode_rating3'] },
    { platform: 'AtCoder', handles: ['atcoder_handle', 'atcoder_handle2', 'atcoder_handle3'], ratingFields: ['atcoder_rating', 'atcoder_rating2', 'atcoder_rating3'] }
  ];

  for (const row of rows) {
    for (const fieldGroup of handleFields) {
      for (let i = 0; i < fieldGroup.handles.length; i++) {
        const handle = row[fieldGroup.handles[i]];
        const ratingField = fieldGroup.ratingFields[i];

        if (handle === null) {
          continue;
        }

        try {
          let user;
          if (fieldGroup.platform === 'Codeforces') {
            user = new CodeforcesUser(handle);
          } else if (fieldGroup.platform === 'LeetCode') {
            user = new LeetCodeUser(handle);
          } else if (fieldGroup.platform === 'AtCoder') {
            user = new AtCoderUser(handle);
          }
          const rating = await user.getRating();
          await connection.query(
            `UPDATE user SET ${ratingField} = '${rating}' WHERE id = ${row.id}`
          );
          console.log(`Update ${fieldGroup.platform} Rating: ${handle} - ${rating}`);
        } catch (err) {
          console.error(`ERROR of Update ${fieldGroup.platform} Rating: ${handle}`);
          console.error(err);
          logError({
            action: `Get ${fieldGroup.platform} Rating`,
            platform: fieldGroup.platform,
            handle: handle,
            error: err,
          });
        }
      }
    }
  }
}


async function refreshAtcoderAvatar() {
  const handleNames = ['atcoder_handle', 'atcoder_handle2', 'atcoder_handle3'];

  for (const handleName of handleNames) {
    const avatarName = transformHandleToAvatar(handleName);
    const atcoderRows = await connection.query(
      `SELECT id, ${handleName} FROM user WHERE ${handleName} IS NOT NULL`
    );

    for (const row of atcoderRows) {
      try {
        const atcoderAvatar = await Avatar.getAtCoderUserAvatar(row[handleName]);
        await connection.query(
          `UPDATE user SET ${avatarName} = '${atcoderAvatar}'
          WHERE ${handleName} = '${row[handleName]}'`
        );
        console.log(`${row[handleName]}: ${atcoderAvatar}`);
      } catch (err) {
        console.error(`ERROR of AtCoder: ${row[handleName]}`);
        console.error(err);
        logError({
          action: `Get AtCoder Avatar`,
          platform: `AtCoder`,
          handle: row[handleName],
          error: err,
        });
      }
    }
  }
}


function refreshAll() {
  refreshCodeforces();
  refreshLeetcode();
  refreshLeetcodeAvatar();
  refreshCodeforcesAvatar();
  refreshAtcoderSubmissions();
  refreshAtcoderProblems();
  refreshAtcoderAvatar();
  refreshRating();

  setTimeout(() => {
    refreshUserDailyStatus();
  }, 200000);
}

export {
  refreshCodeforces,
  refreshLeetcode,
  refreshAtcoderSubmissions,
  refreshAtcoderProblems,
  refreshUserDailyStatus,
  refreshCodeforcesAvatar,
  refreshLeetcodeAvatar,
  refreshAtcoderAvatar,
  refreshRating,
  refreshAll,
};

// refreshAtcoderSubmissions()
// refreshAtcoderProblems()
// refreshRating()
// refreshAtcoderAvatar();